﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace HospitalSystem
{
    public partial class ReportingForeachService : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            
            GridView1.DataSource = PieChartData(0);
            GridView1.DataBind();
            LoadChart(this.PieChartData(1), "piedata");
        }

        //LoadChart For GetJson To Show Graph(using Google Visualization)
        private void LoadChart(DataTable table, string jsName)
        {
            var gdt = new Bortosky.Google.Visualization.GoogleDataTable(table);
            using (var mem = new System.IO.MemoryStream())
            {
                gdt.WriteJson(mem);
                mem.Position = 0;
                using (var sr = new System.IO.StreamReader(mem))
                {
                    Page.ClientScript.RegisterStartupScript(this.GetType(), jsName, string.Format("var {0} = {1};", jsName, sr.ReadToEnd()), true);
                    sr.Close();
                };
            }
        }

        protected DataTable PieChartData(int index)
        {

            DataTable ServiceTemp = new DataTable();
            DataTable BindGridview = new DataTable();
            ServiceTemp.Columns.Add("SVCCode");
            ServiceTemp.Columns.Add("SVCDEC");

            BindGridview.Columns.Add("ServiceCode");
            BindGridview.Columns.Add("Service");
            BindGridview.Columns.Add("Count",typeof(System.Int32));

            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection scon = new SqlConnection(constr);
            scon.Open();
            SqlCommand cmd = new SqlCommand("select SVCCode,SVCDEC from Service", scon);
            SqlDataReader reader1 = cmd.ExecuteReader();
            while (reader1.Read() != false)
            {
                ServiceTemp.Rows.Add(reader1["SVCCode"], reader1["SVCDEC"]);
            }
            reader1.Close();
            for (int i = 0; i < ServiceTemp.Rows.Count; i++)
            {
                SqlCommand cmd2 = new SqlCommand("select count(DISTINCT CID )as Counter from ServiceProvisionVsPerson where PSNumber in(select ServiceProvisionDetails.PSNumber from ServiceProvisionDetails inner join Activity on ServiceProvisionDetails.ACTCode = Activity.ACTCode where Activity.SVCCode = @SVC)", scon);
                cmd2.Parameters.AddWithValue("@SVC", ServiceTemp.Rows[i]["SVCCode"]);
                SqlDataReader reader2 = cmd2.ExecuteReader();
                while (reader2.Read() != false)
                {
                    BindGridview.Rows.Add(ServiceTemp.Rows[i]["SVCCode"], ServiceTemp.Rows[i]["SVCDEC"], reader2["Counter"]);
                }
                reader2.Close();
            }

            scon.Close();

            if (index == 1)
            {
                BindGridview.Columns.Remove("ServiceCode");
            }
            return BindGridview;
        }

        public string GetUrl(object ServiceCode)
        {
            string url = "ReportStudentOfOneService.aspx?ServiceCode=" + Server.UrlEncode(ServiceCode.ToString());

            return url;
        }
    }
}